//
//  DBDaoHelper.m
//  MoreFunTV
//
//  Created by admin on 14-11-7.
//  Copyright (c) 2014年 admin. All rights reserved.
//

#import "DBDaoHelper.h"

@implementation DBDaoHelper


//创建历史记录数据库表
+(BOOL)createAllTable
{
    FMDatabase *db =[DBHelper openDatabase];
    //搜索历史记录表
    BOOL result1 = [db executeUpdate:@"CREATE TABLE IF NOT EXISTS 'TABLE_SearchHistory'('history_id'  INTEGER PRIMARY KEY AUTOINCREMENT,'history_resId' TEXT,'history_title' TEXT)"];
    
    //网页历史记录表
    BOOL result2 = [db executeUpdate:@"CREATE TABLE IF NOT EXISTS 'TABLE_WebHistory'('web_id'  INTEGER PRIMARY KEY AUTOINCREMENT,'web_url' TEXT,'web_title' TEXT)"];
    
    //网页收藏记录表
    BOOL result3 = [db executeUpdate:@"CREATE TABLE IF NOT EXISTS 'TABLE_Collect'('col_id'  INTEGER PRIMARY KEY AUTOINCREMENT,'col_url' TEXT,'col_title' TEXT)"];
    
    //直播台的ID表
    BOOL result4 = [db executeUpdate:@"CREATE TABLE IF NOT EXISTS 'TABLE_LiveID'('live_id'  INTEGER PRIMARY KEY AUTOINCREMENT,'live_ResId' TEXT,'live_url' TEXT,'live_flag' INTEGER,'live_title' TEXT,'live_tupian' TEXT)"];
    
    //直播EPG表
    BOOL result5 = [db executeUpdate:@"CREATE TABLE IF NOT EXISTS 'TABLE_LiveEPG'('epg_id'  INTEGER PRIMARY KEY AUTOINCREMENT,'epg_liveId' INTEGER,'epg_url' TEXT,'epg_title' TEXT,'epg_resId' TEXT,'epg_resTitle' TEXT,'epg_startOn' INTEGER,'epg_endOn' INTEGER,'epg_playUrl' TEXT)"];
    
    //播放记录表
    BOOL result6 = [db executeUpdate:@"CREATE TABLE IF NOT EXISTS 'TABLE_Play'('play_id'  INTEGER PRIMARY KEY AUTOINCREMENT,'playRes_id' TEXT,'play_TuPian' TEXT,'play_title_string' TEXT,'play_title' TEXT,'play_index' TEXT,'play_source' TEXT,'play_time' DATE,'str_history_time' TEXT,'str_play_source' TEXT,'play_classId' INTEGER,'play_url' TEXT)"];
    
    //投射记录表
    BOOL result7 = [db executeUpdate:@"CREATE TABLE IF NOT EXISTS 'TABLE_TouShe'('tou_id'  INTEGER PRIMARY KEY AUTOINCREMENT,'play_id' INTEGER,'tou_title' TEXT,'tou_url' TEXT,'tou_allurl' TEXT,'tou_Tuurl' TEXT)"];
    
    //详情选择记录表
    BOOL result8 = [db executeUpdate:@"CREATE TABLE IF NOT EXISTS 'TABLE_XiangQing'('xiangqing_id'  INTEGER PRIMARY KEY AUTOINCREMENT,'play_res_id' TEXT,'play_index' TEXT,'play_source' TEXT)"];
    
    [db close];
    if (result1 && result2 && result3 && result4 && result5 && result6 && result7 && result8)
    {
        return YES;
    }
    else
    {
        return NO;
    }
}

//插入历史记录信息
+(BOOL )insertSearchHistory:(Search *)searchInfo
{
    FMDatabase *db =[DBHelper openDatabase];
    BOOL result = [db executeUpdate:@"INSERT INTO 'TABLE_SearchHistory'('history_resId','history_title') VALUES(?,?)",searchInfo.strId,searchInfo.strTitle];
    [db close];
    return result;
}

//插入收藏列表信息
+(BOOL)insertCollectInfo:(HistoryInfo *)history
{
    FMDatabase *db =[DBHelper openDatabase];
    BOOL result = [db executeUpdate:@"INSERT INTO 'TABLE_Collect'('col_title','col_url') VALUES(?,?)",history.strTitle,history.strUrl];
    [db close];
    return result;
}

//插入网页历史记录信息
+(BOOL)insertWebHistory:(HistoryInfo *)history
{
    FMDatabase *db =[DBHelper openDatabase];
    BOOL result = [db executeUpdate:@"INSERT INTO 'TABLE_WebHistory'('web_title','web_url') VALUES(?,?)",history.strTitle,history.strUrl];
    [db close];
    return result;
}

//插入直播Id信息
+(BOOL)insertLiveIDWith:(LiveInfo *)live
{
    FMDatabase *db =[DBHelper openDatabase];
    BOOL result = [db executeUpdate:@"INSERT INTO 'TABLE_LiveID'('live_ResId','live_url','live_flag','live_title','live_tupian') VALUES(?,?,?,?,?)",live.strLiveId,live.strPlayUrl,[NSString stringWithFormat:@"%d",live.flag],live.strResTitle,live.strThumbnail];
    [db close];
    return result;
}

//插入EPG信息
+(BOOL)insertEPGWith:(LiveInfo *)live
{
    FMDatabase *db =[DBHelper openDatabase];
    BOOL result = [db executeUpdate:@"INSERT INTO 'TABLE_LiveEPG'('epg_liveId','epg_url','epg_title','epg_resId','epg_resTitle','epg_startOn','epg_endOn','epg_playUrl') VALUES(?,?,?,?,?,?,?,?)",live.strId,live.strThumbnail,live.strTitle,live.strResId,live.strResTitle,live.strStartOn,live.strEndOn,live.strPlayUrl];
    [db close];
    return result;
}

//更新播放记录信息
+(BOOL)updatePlay:(DetailInfo *)detail
{
    FMDatabase *db =[DBHelper openDatabase];
    BOOL result;
    //更新播放记录时间
    if (detail.strHistoryTime && detail.dateTime) {
        result = [db executeUpdate:@"UPDATE 'TABLE_Play' SET 'str_history_time' = ?, 'str_play_source' = ?, 'play_time' = ? where play_id = ? ", detail.strHistoryTime, detail.strPlaySource, detail.dateTime, detail.strPlayId];
    }
    else {
        result = [db executeUpdate:@"UPDATE 'TABLE_Play' SET 'str_play_source' = ?, 'play_time' = ? where play_id = ? ", detail.strPlaySource, detail.dateTime, detail.strPlayId];
    }
    [db close];
    return result;
}

//更新详情选择记录信息
+(BOOL)updateXiangQing:(XiangQingInfo *)detail
{
    FMDatabase *db =[DBHelper openDatabase];
    BOOL result = [db executeUpdate:@"UPDATE 'TABLE_XiangQing' SET 'play_index' = ?, 'play_source' = ? where play_res_id = ? ", detail.strIndex, detail.strSource, detail.strResId];
    [db close];
    return result;
}

//插入播放记录信息
+(BOOL)insertPlay:(DetailInfo *)detail
{
    FMDatabase *db =[DBHelper openDatabase];
    BOOL result = [db executeUpdate:@"INSERT INTO 'TABLE_Play'('playRes_id','play_TuPian','play_title_string','play_title','play_index','play_source','play_time','str_play_source','str_history_time','play_classId','play_url') VALUES(?,?,?,?,?,?,?,?,?,?,?)",detail.strResId,detail.strThumbnail,detail.strTitleString,detail.strTitle,detail.strIndex,detail.strSource,detail.dateTime,detail.strPlaySource,detail.strHistoryTime,detail.strCategoryId,detail.strResUrl];
    [db close];
    return result;
}

//插入详情记录信息
+(BOOL)insertXiangQing:(XiangQingInfo *)detail
{
    FMDatabase *db =[DBHelper openDatabase];
    BOOL result = [db executeUpdate:@"INSERT INTO 'TABLE_XiangQing'('play_res_id','play_index','play_source') VALUES(?,?,?)",detail.strResId,detail.strIndex,detail.strSource];
    [db close];
    return result;
}

//插入投射记录信息
+(BOOL)insertTouShe:(DetailInfo *)detail
{
    FMDatabase *db =[DBHelper openDatabase];
    BOOL result = [db executeUpdate:@"INSERT INTO 'TABLE_TouShe'('tou_title','tou_url','tou_allurl','tou_Tuurl','play_id') VALUES(?,?,?,?,?)",detail.strTitle,detail.strRealUrl,detail.strAllURL,detail.strThumbnail,detail.strPlayId];
    [db close];
    return result;
}

//查询历史记录信息
+(NSMutableArray *)selectSearchHistory
{
    FMDatabase *db =[DBHelper openDatabase];
    NSMutableArray *arr = [[NSMutableArray alloc]init];
    //执行查询语句
    FMResultSet *result = [db executeQuery:@"SELECT * FROM TABLE_SearchHistory"];
    while (result.next)
    {
        //根据列名取得数据
        NSString *strID = [result stringForColumn:@"history_resId"];
        NSString *strTitle = [result stringForColumn:@"history_title"];
        
        Search *search = [[Search alloc]init];
        search.strId = strID;
        search.strTitle = strTitle;
        [arr addObject:search];
    }
    return arr;
}

//查询收藏列表信息
+(NSMutableArray *)selectCollectInfo
{
    FMDatabase *db =[DBHelper openDatabase];
    NSMutableArray *arr = [[NSMutableArray alloc]init];
    //执行查询语句
    FMResultSet *result = [db executeQuery:@"SELECT * FROM TABLE_Collect"];
    while (result.next)
    {
        //根据列名取得数据
        NSString *strTitle = [result stringForColumn:@"col_title"];
        NSString *strUrl = [result stringForColumn:@"col_url"];
        NSString *strId = [result stringForColumn:@"col_id"];
        
        HistoryInfo *history = [[HistoryInfo alloc]init];
        history.strTitle = strTitle;
        history.strUrl = strUrl;
        history.strId = strId;
        [arr addObject:history];
    }
    return arr;
    
}

//查询最后加入的一条网页历史记录信息
+(HistoryInfo *)selectLastWebHistory
{
    FMDatabase *db =[DBHelper openDatabase];
    //执行查询语句
    FMResultSet *result = [db executeQuery:@"SELECT * FROM TABLE_WebHistory ORDER BY web_id DESC"];
    HistoryInfo *history = [[HistoryInfo alloc]init];
    while (result.next)
    {
        //根据列名取得数据
        NSString *strUrl = [result stringForColumn:@"web_url"];
        NSString *strTitle = [result stringForColumn:@"web_title"];
        NSString *strId = [result stringForColumn:@"web_id"];
        
        history.strTitle = strTitle;
        history.strUrl = strUrl;
        history.strId = strId;
        break;
    }
    return history;
}

//查询网页历史记录信息
+(NSMutableArray *)selectWebHistory
{
    FMDatabase *db =[DBHelper openDatabase];
    NSMutableArray *arr = [[NSMutableArray alloc]init];
    //执行查询语句
    FMResultSet *result = [db executeQuery:@"SELECT * FROM TABLE_WebHistory"];
    while (result.next)
    {
        //根据列名取得数据
        NSString *strUrl = [result stringForColumn:@"web_url"];
        NSString *strTitle = [result stringForColumn:@"web_title"];
        NSString *strId = [result stringForColumn:@"web_id"];
        
        HistoryInfo *history = [[HistoryInfo alloc]init];
        history.strTitle = strTitle;
        history.strUrl = strUrl;
        history.strId = strId;
        [arr addObject:history];
    }
    return arr;
}

//查询直播的ID
+(NSMutableArray *)selectLiveID:(int)flag
{
    FMDatabase *db =[DBHelper openDatabase];
    NSMutableArray *arr = [[NSMutableArray alloc]init];
    //执行查询语句
    FMResultSet *result = [db executeQuery:@"SELECT * FROM TABLE_LiveID WHERE live_flag = ?",[NSString stringWithFormat:@"%d",flag]];
    while (result.next)
    {
        //根据列名取得数据
        NSString *strId = [result stringForColumn:@"live_ResId"];
        NSString *strUrl = [result stringForColumn:@"live_url"];
        NSString *strTitle = [result stringForColumn:@"live_title"];
        NSString *strTupian = [result stringForColumn:@"live_tupian"];
        LiveInfo *live = [[LiveInfo alloc]init];
        live.strLiveId = strId;
        live.strPlayUrl = strUrl;
        live.strResTitle = strTitle;
        live.strThumbnail = strTupian;
        [arr addObject:live];
    }
    return arr;
}

//查询正在播放的EPG信息
+(LiveInfo *)selectNowEpgInfo:(LiveInfo *)live
{
    FMDatabase *db =[DBHelper openDatabase];
    //执行查询语句
    FMResultSet *result = [db executeQuery:@"SELECT * FROM TABLE_LiveEPG WHERE epg_endOn >= ? AND epg_startOn <= ? AND epg_resId = ?",live.strLiveTime,live.strLiveTime,live.strLiveId];
    
    LiveInfo *liv = [[LiveInfo alloc]init];
    while (result.next)
    {
        //根据列名取得数据
        NSString *strTitle = [result stringForColumn:@"epg_title"];
//        NSString *strUrl = [result stringForColumn:@"epg_url"];
        NSString *strId = [NSString stringWithFormat:@"%d",[result intForColumn:@"epg_liveId"]];
//        NSString *strResTitle = [result stringForColumn:@"epg_resTitle"];
//        NSString *strResId = [result stringForColumn:@"epg_resId"];
//        NSString *strPlayUrl = [result stringForColumn:@"epg_playUrl"];
        
        liv.strTitle = strTitle;
        liv.strId = strId;
//        liv.strResTitle = strResTitle;
//        liv.strThumbnail = strUrl;
//        liv.strResId = strResId;
    }
    return liv;
}

//查询所有的EPG信息
+(LiveInfo *)selectAllEpgInfo:(LiveInfo *)live
{
    FMDatabase *db =[DBHelper openDatabase];
    //执行查询语句
    FMResultSet *result = [db executeQuery:@"SELECT * FROM TABLE_LiveEPG WHERE epg_liveId = ? AND epg_resId = ?",live.strNextId,live.strResId];
    
    while (result.next)
    {
        //根据列名取得数据
        NSString *strTitle = [result stringForColumn:@"epg_title"];
        
        LiveInfo *liv = [[LiveInfo alloc]init];
//        liv.strResId = live.strResId;
//        liv.strResTitle = live.strResTitle;
//        liv.strId = live.strId;
//        liv.strTitle = live.strTitle;
//        liv.strThumbnail = live.strThumbnail;
        liv.strNextTitle = strTitle;
//        liv.strPlayUrl = live.strPlayUrl;
        return liv;
    }
    return nil;
}

//查询最新播放记录的play_id
+(NSString *)selectStrPlayId
{
    FMDatabase *db =[DBHelper openDatabase];
    //执行查询语句
    FMResultSet *result = [db executeQuery:@"SELECT * FROM TABLE_Play order by play_time desc"];
    
    NSString *strId = @"";
    
    NSMutableArray *arr = [[NSMutableArray alloc]init];
    while (result.next)
    {
        NSString *strPlayId = [result stringForColumn:@"play_id"];
        [arr addObject:strPlayId];
    }
    if (arr.count > 0) {
        strId = arr[0];
    }
    return strId;
}

//根据play_res_id查询详情记录的最近一条信息
+(XiangQingInfo *)selectStrResId:(NSString *)strResId
{
    FMDatabase *db =[DBHelper openDatabase];
    NSLog(@"strResId:%@",strResId);
    //执行查询语句
    FMResultSet *result = [db executeQuery:@"SELECT * FROM TABLE_XiangQing WHERE play_res_id = ?",[NSString stringWithFormat:@"%@",strResId]];
    
    XiangQingInfo *info = [[XiangQingInfo alloc] init];
    
    while (result.next)
    {
        NSString *strIndex = [result stringForColumn:@"play_index"];
        NSString *strSource = [result stringForColumn:@"play_source"];
        info.strResId = strResId;
        info.strIndex = strIndex;
        info.strSource = strSource;
    }
    
    return info;
}

//查询所有的播放记录信息
+(NSMutableArray *)selectAllPlayInfo
{
    FMDatabase *db =[DBHelper openDatabase];
    //执行查询语句
    FMResultSet *result = [db executeQuery:@"SELECT * FROM TABLE_Play order by play_time desc"];
    
    NSMutableArray *arr = [[NSMutableArray alloc]init];
    while (result.next)
    {
        //根据列名取得数据
        NSString *strResId = [result stringForColumn:@"playRes_id"];
        NSString *strTuPian = [result stringForColumn:@"play_TuPian"];
        NSString *strTitleStirng = [result stringForColumn:@"play_title_string"];
        NSString *strTitle = [result stringForColumn:@"play_title"];
        //集数－新增字段
        NSString *strIndex = [result stringForColumn:@"play_index"];
        //来源－新增字段
        NSString *strSource = [result stringForColumn:@"play_source"];
        NSDate *dateTime = [result dateForColumn:@"play_time"];
        NSString *strHistoryTime = [result stringForColumn:@"str_history_time"];
        int classID = [result intForColumn:@"play_classId"];
        NSString *strUrl = [result stringForColumn:@"play_url"];
        NSString *strPlayId = [result stringForColumn:@"play_id"];
        NSString *strPlaySource = [result stringForColumn:@"str_play_source"];
        
        DetailInfo *detail = [[DetailInfo alloc]init];
        detail.strResId = strResId;
        detail.strTitleString = strTitleStirng;
        detail.strTitle = strTitle;
        //集数－新增字段
        detail.strIndex = strIndex;
        //来源－新增字段
        detail.strSource = strSource;
        detail.strThumbnail = strTuPian;
        detail.dateTime = dateTime;
        detail.strHistoryTime = strHistoryTime;
        detail.classId = classID;
        detail.strResUrl = strUrl;
        detail.strPlayId = strPlayId;
        detail.strPlaySource = strPlaySource;
        
        [arr addObject:detail];
    }
    return arr;
}

//查询投射信息是否已经插入
+(BOOL)selectTouShe:(NSString *)strTitle
{
    FMDatabase *db =[DBHelper openDatabase];
    NSString *str = @"";
    //执行查询语句
    FMResultSet *result = [db executeQuery:@"SELECT * FROM TABLE_TouShe WHERE tou_title = ?",strTitle];
    while (result.next)
    {
        //根据列名取得数据
        str = [result stringForColumn:@"tou_title"];
    }
    
    if ([str isEqual:@""]) {
        return NO;
    }
    else{
        return YES;
    }
}

//查询所有的投射记录信息
+(NSMutableArray *)selectAllTouShe
{
    FMDatabase *db =[DBHelper openDatabase];
    //执行查询语句
    FMResultSet *result = [db executeQuery:@"SELECT * FROM TABLE_TouShe"];
    
    NSMutableArray *arr = [[NSMutableArray alloc]init];
    while (result.next)
    {
        //根据列名取得数据
        NSString *strId = [result stringForColumn:@"tou_id"];
        NSString *strTitle = [result stringForColumn:@"tou_title"];
        NSString *strUrl = [result stringForColumn:@"tou_url"];
        NSString *strAllUrl = [result stringForColumn:@"tou_allurl"];
        NSString *strTuPianUrl = [result stringForColumn:@"tou_Tuurl"];
        NSString *strPlayId = [result stringForColumn:@"play_id"];
        
        DetailInfo *deta = [[DetailInfo alloc]init];
        deta.strId = strId;
        deta.strTitle = strTitle;
        deta.strRealUrl = strUrl;
        deta.strAllURL = strAllUrl;
        deta.strThumbnail = strTuPianUrl;
        deta.strPlayId = strPlayId;
        
        [arr addObject:deta];
    }
    return arr;
}

//删除第一条历史记录信息
+(void)deleteSearchHistoryMin
{
    FMDatabase *db = [DBHelper openDatabase];
    [db executeUpdate:@"DELETE FROM TABLE_SearchHistory WHERE history_id IN (SELECT MIN(history_id) FROM TABLE_SearchHistory)"];
    [db close];
}

//删除全部历史记录信息
+(void)deleteSearchHistoryAll
{
    FMDatabase *db = [DBHelper openDatabase];
    [db executeUpdate:@"DELETE FROM TABLE_SearchHistory"];
    [db close];
}

//删除一条收藏列表记录
+(void)deleteCollecet:(HistoryInfo *)history
{
    FMDatabase *db = [DBHelper openDatabase];
    [db executeUpdate:@"DELETE FROM TABLE_Collect WHERE col_id = ?",history.strId];
    [db close];
}

//删除一条网页历史记录
+(void)deleteHiistory:(HistoryInfo *)history
{
    FMDatabase *db = [DBHelper openDatabase];
    [db executeUpdate:@"DELETE FROM TABLE_WebHistory WHERE web_id = ?",history.strId];
    [db close];
}

//删除全部收藏列表记录
+(void)deleteAllCollecet
{
    FMDatabase *db = [DBHelper openDatabase];
    [db executeUpdate:@"DELETE FROM TABLE_Collect"];
    [db close];
}

//删除全部网页历史记录
+(void)deleteAllHiistory
{
    FMDatabase *db = [DBHelper openDatabase];
    [db executeUpdate:@"DELETE FROM TABLE_WebHistory"];
    [db close];
}

//删除全部LiveID信息
+(void)deleteAllLiveID
{
    FMDatabase *db = [DBHelper openDatabase];
    [db executeUpdate:@"DELETE FROM TABLE_LiveID"];
    [db close];
}

//删除全部EPG信息
+(void)deleteAllEPG
{
    FMDatabase *db = [DBHelper openDatabase];
    [db executeUpdate:@"DELETE FROM TABLE_LiveEPG"];
    [db close];
}

//删除全部播放记录信息
+(void)deleteAllTABLEPlay
{
    FMDatabase *db = [DBHelper openDatabase];
    [db executeUpdate:@"DELETE FROM TABLE_Play"];
    [db close];
}

//删除一条投射记录
+(void)deleteTouShe:(DetailInfo *)deta
{
    FMDatabase *db = [DBHelper openDatabase];
    [db executeUpdate:@"DELETE FROM TABLE_TouShe WHERE tou_id = ?",deta.strId];
    [db close];
}

//删除全部投射记录
+(void)deleteAllTouShe
{
    FMDatabase *db = [DBHelper openDatabase];
    [db executeUpdate:@"DELETE FROM TABLE_TouShe"];
    [db close];
}
@end
